package com.core.extend.excel;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

public class ExcelUtils {
	

	private static final String EXCEL_XLS = "xls";
	private static final String EXCEL_XLSX = "xlsx";
	
	/**
	 * 根据excel的版本，获取相应的Workbook
	 *
	 * @param file
	 * @return
	 */
	public static Workbook getWorkbook(MultipartFile file) throws IOException {
		Workbook wb = null;
		InputStream fis = file.getInputStream();
		if (file.getOriginalFilename().endsWith(EXCEL_XLS)) //2003
		{
			wb = new HSSFWorkbook(fis);
		} else if (file.getOriginalFilename().endsWith(EXCEL_XLSX)) {
			wb = new XSSFWorkbook(fis);//2007 2010
		}
		if (fis != null) {
			fis.close();
		}
		return wb;
	}

	/**
	 * 读取给定sheet的内容
	 * 描述：
	 * 读取excel文件中的指定名称的表格 用于没有单元格合并的表格，且 (titleOfRow,titleOfColumn)位置为读取内容的起始位置的情况
	 * 每一行构成一个map(key值是列标题，value是列值)。没有值的单元格其value值为null。
	 * 返回结果最外层的list对应一个sheet页，第二层的map对应sheet页中的一行。
	 *
	 * @param sheet
	 * @return
	 */
	private static List<Map<Integer, Object>> readSheet(Sheet sheet, int titleInRow, int titleInColumn, Workbook book) {
		List<Map<Integer, Object>> sheetList = null;
		sheetList = new ArrayList<Map<Integer, Object>>();
		List<String> titles = new ArrayList<>();
		int rowSize = sheet.getLastRowNum() + 1;
		for (int i = titleInRow; i < rowSize; i++) {
			Row row = sheet.getRow(i);
			if (row == null)
				continue;
			;//略过空行
			int cellSize = row.getLastCellNum();
			if (i == titleInRow) //标题行
			{
				for (int j = titleInColumn; j < cellSize; j++) {
					Cell cell = row.getCell(j);
					if (cell != null) {
						titles.add(cell.toString());
					}
				}
			} else { //对应每一行的数据
				Map<Integer, Object> rowDataMap = new LinkedHashMap<Integer, Object>();
				for (int j = titleInColumn; j < titleInColumn + titles.size(); j++) {
					Cell cell = row.getCell(j);
					String value = null;
					CellType cellType = null;
					if (cell == null) {
						continue;
					}
					cellType = cell.getCellTypeEnum();
					switch (cellType) {
					case STRING:
						value = superOrSubScript2007(cell,book);
						break;
					case NUMERIC: //包含日期和普通数字
					if (DateUtil.isCellDateFormatted(cell)) {
						Date date = cell.getDateCellValue();
						DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
						value = df.format(date);
					} else {
						double cellValue = cell.getNumericCellValue();
						value = String.valueOf(cellValue);
					}
					break;
					case FORMULA:
						cell.setCellType(CellType.STRING);
						value = cell.getStringCellValue();
						break;
					case BOOLEAN:
						value = String.valueOf(cell.getBooleanCellValue());
						break;
					default:
						if (cell != null) {
							value = cell.toString();
						}
					}
//					String key = titles.get(j - titleInColumn);
					rowDataMap.put(j, value);
					rowDataMap.put(999, sheet.getSheetName());
				}
				sheetList.add(rowDataMap);
			}
		}
		return sheetList;
	}


	public static String superOrSubScript2007(Cell cell, Workbook book) {
		XSSFWorkbook workbook = null;
		XSSFFont font = null;
		XSSFRichTextString rts = null;
		XSSFCellStyle style = null;
		int runIndex = 0;
		int runLength = 0;
		String value = "";
		// 处理上下标
		workbook = (XSSFWorkbook) book;
		// 判断当前单元格的内容是否为数字类型，如果是转换成字符串型
		if (cell != null && cell.getCellTypeEnum() == CellType.NUMERIC) {
			String stringCellValue = cell.getStringCellValue();
			cell.setCellValue(stringCellValue);
		}

		// 获取单元格中的数据
		if (cell != null) {
			rts = (XSSFRichTextString) cell.getRichStringCellValue();
			style = (XSSFCellStyle) cell.getCellStyle();
			font = style.getFont();
		}
		if (rts.numFormattingRuns() > 1) {
			for (int k = 0; k < rts.numFormattingRuns(); k++) {
				runLength = rts.getLengthOfFormattingRun(k);
				runIndex = rts.getIndexOfFormattingRun(k);
				String temp = rts.toString().substring(runIndex, (runIndex + runLength));
				try {
					font = rts.getFontOfFormattingRun(k);
					font.getTypeOffset();
				} catch (NullPointerException npe) {
					font = workbook.getFontAt(XSSFFont.DEFAULT_CHARSET);
					font.setTypeOffset(XSSFFont.SS_NONE);
				}
				if (font.getTypeOffset() == XSSFFont.SS_SUPER) {
					temp = "<sup>" + temp + "</sup>";
				}
				if (font.getTypeOffset() == XSSFFont.SS_SUB) {
					temp = "<sub>" + temp + "</sub>";
				}
				value += temp;
			}
			return value;
		}
		return cell.toString();
	}


	/**
	 * 读取excel文件中的全部表格
	 * 描述：适用于没有单元格合并的excel，并且 (titleOfRow,titleOfColumn)位置为读取内容的起始位置的情况
	 * 每一行构成一个map，key值是列标题，value是列值。没有值的单元格其value值为null
	 * 返回结果最外层list对应excel文件，第二层Iterable对应sheet页，第三层map对应sheet页中一行
	 *
	 * @param filePath 文件路径
	 * @param sheetCnt 读取的文件中前sheetCnt个sheet数据。如果值为-1，则读取所有的sheet，否则，读取前sheetCnt个sheet的数据。
	 * @return
	 * @throws Exception
	 */
	public static List<Map<Integer, Object>> readExcelWithFiexPos(MultipartFile file, int titleInRow, int titleInColumn, int sheetCnt) throws IOException {
		Workbook wb = null;
		try {
			List<Map<Integer, Object>> excelData = new ArrayList<>();
			if (file.getOriginalFilename().endsWith(EXCEL_XLS) || file.getOriginalFilename().endsWith(EXCEL_XLSX)) {
				wb = getWorkbook(file);
				int sheetSize = sheetCnt == -1 ? wb.getNumberOfSheets() : sheetCnt;
				//遍历sheet
				for (int i = 0; i < sheetSize; i++) {
					Sheet sheet = wb.getSheetAt(i);
					List<Map<Integer, Object>> theSheetData = readSheet(sheet, titleInRow, titleInColumn,wb);
					excelData.addAll(theSheetData);
				}
			}
			return excelData;
		} catch (FileNotFoundException e) {
			throw e;
		} finally {
			if (wb != null) {
				wb.close();
			}
		}
	}

	public static void main(String[] args) throws FileNotFoundException, IOException {
//		String path="D:\\work\\五院\\环境探测\\需求分析阶段\\20230826\\数据\\23.9.25 PM数据定稿(1).xlsx";
//		List<Map<Integer, Object>> l = readExcelWithFiexPos(path,0,0,-1);
//		for (int i = 0; i < l.size(); i++) { 
//		}
		
	}
}